﻿using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using TruckScale.Helpers;
using static TruckScale.Helpers.LogHelper;

namespace TruckScale.Models
{
  public class CustomerModel
  {
    public int? CustomerId { get; set; }
    public string CustomerNum { get; set; }
    public string CustomerName { get; set; }
    public string CustomerType { get; set; } = string.Empty;
    //public string CustomerLevel { get; set; }
    public string Comment { get; set; }
    public bool IsValid { get; set; } = true;
    public string By1 { get; set; }
    public string By2 { get; set; }
    public string By3 { get; set; }

    public static List<CustomerModel> SelectAllCustomer()
    {
      using (IDbConnection cnn = DBHelper.DbConnection())
      {
        try
        {
          string sql = "select * from customer;";
          IEnumerable<CustomerModel> output = cnn.Query<CustomerModel>(sql);
          return output.ToList();
        }
        catch (Exception e)
        {
          SetLog(e.Message, LogType.OperationLogger, LogLevel.Error);
          return null;
        }
      }
    }
    public static List<CustomerModel> SelectValidCustomer()
    {
      using (IDbConnection cnn = DBHelper.DbConnection())
      {
        try
        {
          string sql = "select * from customer where is_valid = 1;";
          IEnumerable<CustomerModel> output = cnn.Query<CustomerModel>(sql);
          return output.ToList();
        }
        catch (Exception e)
        {
          SetLog(e.Message, LogType.OperationLogger, LogLevel.Error);
          return null;
        }
      }
    }
    public static List<CustomerModel> SelectCustomerByType(string type)
    {
      using (IDbConnection cnn = DBHelper.DbConnection())
      {
        try
        {
          string sql = $"select * from customer where is_valid = 1 and customer_type like '%{type}%';";
          IEnumerable<CustomerModel> output = cnn.Query<CustomerModel>(sql);
          return output.ToList();
        }
        catch (Exception e)
        {
          SetLog(e.Message, LogType.OperationLogger, LogLevel.Error);
          return null;
        }
      }
    }
    public static List<CustomerModel> SelectCustomerByType(string type, string keyword)
    {
      using (IDbConnection cnn = DBHelper.DbConnection())
      {
        try
        {
          string sql = $"select * from customer where is_valid = 1 and customer_type like '%{type}%' and (customer_name like '%{keyword}%' or customer_num like '%{keyword}%');";
          IEnumerable<CustomerModel> output = cnn.Query<CustomerModel>(sql);
          return output.ToList();
        }
        catch (Exception e)
        {
          SetLog(e.Message, LogType.OperationLogger, LogLevel.Error);
          return null;
        }
      }
    }
    public static int SelectUsedCustomer(CustomerModel customer)
    {
      using (IDbConnection cnn = DBHelper.DbConnection())
      {
        try
        {
          string sql1 = $"select count(*) from weighrecord where is_valid = 1 and (kh = '{customer.CustomerId}' or kh2 = '{customer.CustomerId}' or kh3 = '{customer.CustomerId}' or kh4 = '{customer.CustomerId}');";
          int output = cnn.Query<int>(sql1).FirstOrDefault();
          string sql2 = $"select count(*) from car where is_valid = 1 and car_owner = '{customer.CustomerId}'";
          output += cnn.Query<int>(sql2).FirstOrDefault();
          return output;
        }
        catch (Exception e)
        {
          SetLog(e.Message, LogType.OperationLogger, LogLevel.Error);
          return -1;
        }
      }
    }
    public static int InsertCustomer(CustomerModel customer)
    {
      using (IDbConnection cnn = DBHelper.DbConnection())
      {
        try
        {
          string sql = "insert into customer (customer_num, customer_name, customer_type, comment) values (@CustomerNum, @CustomerName, @CustomerType, @Comment);";
          return cnn.Execute(sql, customer);
        }
        catch (Exception e)
        {
          SetLog(e.Message, LogType.OperationLogger, LogLevel.Error);
          return -1;
        }
      }
    }
    public static int UpdateCustomer(CustomerModel customer)
    {
      using (IDbConnection cnn = DBHelper.DbConnection())
      {
        try
        {
          string sql = "update customer set customer_num = @CustomerNum, customer_name = @CustomerName, customer_type = @CustomerType, comment = @Comment , is_valid = @IsValid where customer_id = @CustomerId;";
          return cnn.Execute(sql, customer);
        }
        catch (Exception e)
        {
          SetLog(e.Message, LogType.OperationLogger, LogLevel.Error);
          return -1;
        }
      }
    }
    public static int DeleteCustomer(CustomerModel customer)
    {
      using (IDbConnection cnn = DBHelper.DbConnection())
      {
        try
        {
          string sql = $"delete from customer where customer_id = '{customer.CustomerId}'";
          return cnn.Execute(sql);
        }
        catch (Exception e)
        {
          SetLog(e.Message, LogType.OperationLogger, LogLevel.Error);
          return -1;
        }
      }
    }
  }
}
